﻿

CREATE proc [Projects].[GetBusinessCasesShortList]
@CreatedFrom		datetime2(7)	= null,
@CreatedTo			datetime2(7)	= null,
@CustomerGroup		int				= null,
@CustomerSource		int				= null,
@FileUser			int				= null,
@Intern				bit				= null,
@ProjectStrasse		nvarchar(50)	= null,
@EPLZ				int				= null,
@ProjectStatus		tinyint			= null,
@Purchaser			int				= null

as
begin
	set nocount on
	IF OBJECT_ID (N'#tmpBCShortList') IS NOT NULL
	begin
		drop table #tmpBCShortList	
	end

	create table #tmpBCShortList (
		ProjectID			int,
		CreatedBy			nvarchar(50),
		CreatedOn			datetime2(7),
		CustomerGroup		tinyint,
		CustomerSource		nvarchar(100),
		FileUser			nvarchar(50),
		FinishDate			datetime2(7),
		FirstContactDate	datetime2(7),
		ImplementationDate	datetime2(7),
		Intern				bit,
		Purchaser			nvarchar(50),
		InvoiceRecipient	nvarchar(50),
		LastChangedBy		nvarchar(50),
		LastChangedOn		datetime2(7),
		OrderDate			datetime2(7),
		ProjectAddress		nvarchar(100),
		ProjectStatus		int,
		ProjectStatusText	nvarchar(50),
		ShortName			nvarchar(50),
		ErloesSumNetto		decimal(11,2),
		CostSummary			decimal(11,2),
		ExtraCostSummary	decimal(11,2),
		ResultDB			decimal(11,2),
		TimeSummary			decimal(11,2),
	)

	;with selBC
	as
	(
		select	p.ProjectID, 
				createUser.LoginName	'CreatedBy',
				p.CreateOn,
				p.CustomerGroup,
				rtrim(cs.CustomerSourceShort)	'CustomerSource',
				rtrim(fileUser.LoginName)		'FileUser',
				p.FinishDate,
				p.FirstContactDate,
				p.ImplementationDate,
				p.Intern, 
				case when pur.Prefix != '' 
					then pur.Prefix + ' '
					else '' 
				end
				+ pur.[Name]			'Purchaser',
				case when invRec.Prefix != '' 
					then invRec.Prefix + ' '
					else '' 
				end
				+ invREc.[Name]			'InvoiceRecipient',
				lastChangeUser.LoginName	'LastChangedBy',
				p.LastChangedOn,
				p.OrderDate,
				coalesce(plz.PLZ + ' ','') + coalesce(plz.Ort,'') + coalesce(', ' + p.[Address],'')	'ProjectAddress',
				p.ProjectStatus			'ProjectStatus',
				ps.StateText			'ProjectStatusText',
				p.ShortName, 
				pv.ErloesSumNetto,
				pv.CostSummary,
				pv.ExtraCostSummary,
				pv.ResultDB,
				pv.TimeSummary
			
		from	Projects.datProjects p
				inner join Projects.datProjectsValues pv on
					pv.ProjectID = p.ProjectID
				inner join [User].datUser createUser on
					createUser.UserID = p.CreateBy
				inner join [dbo].[domCustomerSource] cs on
					cs.CustomerSourceID = p.CustomerSource
				inner join [User].datUser fileUser on
					fileUser.UserID = p.FileUserID
				inner join dbo.Organisation invRec on
					invRec.OrgKey = p.InvoiceToID
				inner join [User].datUser lastChangeUser on
					lastChangeUser.UserID = p.LastChangedBy
				inner join dbo.Postleitzahl plz on
					plz.EPLZ = p.EPLZ
				inner join Projects.domProjectStatus ps on
					ps.ProjectStatus = p.ProjectStatus
				inner join dbo.Organisation pur on
					pur.OrgKey = p.PurchaserID
		where	(p.CreateOn >= @CreatedFrom or @CreatedFrom is null)
				and (p.CreateOn < DATEADD(DAY, 1, @CreatedTo) or @CreatedTo is null)
				and (p.Intern = @Intern or @Intern is null)
				and (p.EPLZ = @EPLZ or @EPLZ is null)
				and (p.PurchaserID = @Purchaser or @Purchaser is null)
				and (p.FileUserID = @FileUser or @FileUser is null)
				and (p.[Address] like @ProjectStrasse + '%' or @ProjectStrasse is null)
				and (p.CustomerSource = @CustomerSource or @CustomerSource is null)
	)
	insert into #tmpBCShortList (
		ProjectID,
		CreatedBy,
		CreatedOn,
		CustomerGroup,
		CustomerSource,
		FileUser,
		FinishDate,
		FirstContactDate,
		ImplementationDate,
		Intern,
		Purchaser,
		InvoiceRecipient,
		LastChangedBy,
		LastChangedOn,
		OrderDate,
		ProjectAddress,
		ProjectStatus,
		ProjectStatusText,
		ShortName,
		ErloesSumNetto,
		CostSummary,
		ExtraCostSummary,
		ResultDB,
		TimeSummary
	)
	select	bc.ProjectID,
			bc.CreatedBy,
			bc.CreateOn,
			bc.CustomerGroup,
			bc.CustomerSource,
			bc.FileUser,
			bc.FinishDate,
			bc.FirstContactDate,
			bc.ImplementationDate,
			bc.Intern,
			bc.Purchaser,
			bc.InvoiceRecipient,
			bc.LastChangedBy,
			bc.LastChangedOn,
			bc.OrderDate,
			bc.ProjectAddress,
			bc.ProjectStatus,
			bc.ProjectStatusText,
			bc.ShortName,
			bc.ErloesSumNetto,
			bc.CostSummary,
			bc.ExtraCostSummary,
			bc.ResultDB,
			bc.TimeSummary
	from	selBC bc


	if @CustomerGroup is not null
	begin
		delete	#tmpBCShortList
		where	CustomerGroup != @CustomerGroup
	end

	if @ProjectStatus is not null
	begin
		delete	#tmpBCShortList
		where	ProjectStatus != @ProjectStatus
	end


	select	sl.ProjectID,
			sl.CreatedBy,
			sl.CreatedOn,
			sl.CustomerGroup,
			sl.CustomerSource,
			sl.FileUser,
			sl.FinishDate,
			sl.FirstContactDate,
			sl.ImplementationDate,
			sl.Intern,
			sl.Purchaser,
			sl.InvoiceRecipient,
			sl.LastChangedBy,
			sl.LastChangedOn,
			sl.OrderDate,
			sl.ProjectAddress,
			sl.ProjectStatus,
			sl.ProjectStatusText,
			sl.ShortName,
			sl.ErloesSumNetto,
			sl.CostSummary,
			sl.ExtraCostSummary,
			sl.ResultDB,
			sl.TimeSummary
	from	#tmpBCShortList sl
	--where	sl.FileUser = @FileUser or @FileUser is null
	
end
GO


